/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Question;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import util.DBUtil;
import org.apache.log4j.Logger;

/**
 *
 * @author PhucAn
 */
public class QuestionDAO {

    Connection conn;
    private int totalRecords = 0;
    String sqlSelectByID = "select * from Question where id=?";
    String sqlCreate = "insert into Question(category_id, content, mark, is_single_choice, hard_level) values(?, ?, ?, ?, ?)";
    String sqlUpdate = "update Question set category_id=?, content=?, mark=?, is_single_choice=?, hard_level=? where id=?";
    String sqlDelete = "delete from Question where id=?";
    Logger logger = Logger.getLogger(QuestionDAO.class);

    public QuestionDAO() {
    }

    /**
     * get a question by specifying identifier value
     * @param id
     * @return Question object
     */
    public Question getQuestion(int id) {
        Question q = new Question();
        try {
            conn = DBUtil.getConnection();

            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }
            PreparedStatement ps = conn.prepareStatement(sqlSelectByID);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                q.setId(id);
                q.getCategory().setId(rs.getInt("category_id"));
                q.setContent(rs.getString("content"));
                q.setMark(rs.getFloat("mark"));
                q.setSingleChoice(rs.getBoolean("is_single_choice"));
                q.setLevel(rs.getByte("hard_level"));
            }
            DBUtil.disconnect();

        } catch (SQLException ex) {
            logger.info("SQL error: " + ex.getMessage());
        } catch (Exception ex) {
            logger.info("Error: " + ex.getMessage());
        }
        return q;
    }

    /**
     * 
     * @param currentPage current page to select
     * @param pageSize number of records per page
     * @return list of questions
     */
    public ArrayList<Question> getQuestions(int currentPage, int pageSize) {
        ArrayList<Question> list = new ArrayList<Question>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }
            QueryObject qo = new QueryObject("Question", "id", "id");
            qo.setColumns("id, category_id, content, mark, is_single_choice,hard_level");
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);
            qo.setSortDirection("desc");
            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            //ResultSetMetaData meta=rs.getMetaData();
            //String name=(meta.getColumnName(6));
            while (rs.next()) {
                Question q = new Question();
                q.setId(rs.getInt(1));
                q.getCategory().setId(rs.getInt(2));
                q.setContent(rs.getString(3));
                q.setMark(rs.getFloat(4));
                q.setSingleChoice(rs.getBoolean(5));
                q.setLevel(rs.getByte(6));
                list.add(q);
            }
            totalRecords = qo.getTotals();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    public ArrayList<Question> getQuestions(int currentPage, int pageSize, String keyword) {
        ArrayList<Question> list = new ArrayList<Question>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }
            QueryObject qo = new QueryObject("Question", "id", "id");
            qo.setColumns("id, category_id, content, mark, is_single_choice,hard_level");
            keyword = " '%" + keyword + "%' ";
            qo.setWhereClause("content like" + keyword);
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);

            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Question q = new Question();
                q.setId(rs.getInt(1));
                q.getCategory().setId(rs.getInt(2));
                q.setContent(rs.getString(3));
                q.setMark(rs.getFloat(4));
                q.setSingleChoice(rs.getBoolean(5));
                q.setLevel(rs.getByte(6));
                list.add(q);
            }
            totalRecords = qo.getTotals();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    /**
     * add new question
     * @param q question
     * @return greater than 0 value if the action is success, else return 0
     */
    public int create(Question q) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlCreate);
            ps.setInt(1, q.getCategory().getId());
            ps.setString(2, q.getContent());
            ps.setFloat(3, q.getMark());
            ps.setBoolean(4, q.isSingleChoice());
            ps.setByte(5, q.getLevel());
            effectedRows = ps.executeUpdate();

            ps.close();
            ps = conn.prepareStatement("select max(id) from Question");
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                q.setId(rs.getInt(1));
            }
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * update specified question
     * @param q question to update
     * @return 1 or greater if action is successful, else return 0
     */
    public int update(Question q) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlUpdate);
            ps.setInt(1, q.getCategory().getId());
            ps.setString(2, q.getContent());
            ps.setFloat(3, q.getMark());
            ps.setBoolean(4, q.isSingleChoice());
            ps.setByte(5, q.getLevel());
            ps.setInt(6, q.getId());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * delete a question by specifying identifier value
     * @param id
     * @return 1 or greater value if action is successful , else return 0
     */
    public int delete(int id) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlDelete);
            ps.setInt(1, id);
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * @return the totalRecords
     */
    public int getTotalRecords() {
        return totalRecords;
    }

    /**
     * @param totalRecords the totalRecords to set
     */
    public void setTotalRecords(int totalRecords) {
        this.totalRecords = totalRecords;
    }
}
